import datetime
import time

from DBUtils import DBManager
import xlrd

dbManager = DBManager(host='localhost',
                      user='root',
                      password='20203219',
                      database='test',
                      charset='utf8mb4')

def create_table():
    table_name = "user_info"
    table_columns = {
        "id": "int(11)",
        "idcard": "varchar(50)",
        "username": "varchar(50)",
        "realname": "varchar(50)",
        "pwd": "varchar(50)",
        "telphone": "varchar(12)",
        "email": "varchar(100)",
        "age": "int(11)",
        "sex": "varchar(20)",
        "address": "varchar(200)",
        "hiredate": "date",
        "sal": "double(9,2)",
        "job": "varchar(100)",
        "company": "varchar(100)"
    }
    dbManager.create_table(table_name, table_columns)


# 把将百度员工6万条数据，xlrd读取出来并使用pymysql写入到数据库中
def test2():
    file_path = "../ExcelPractise/baidu-员工的人员信息.xls"
    workbook = xlrd.open_workbook(file_path)
    sheet = workbook.sheet_by_index(0)
    rows = sheet.nrows
    cols = sheet.ncols
    attribute_method = {0: int, 1: str, 2: str, 3: str, 4: str, 5: str, 6: str, 7: int, 8: str, 9: str,
                        10: datetime.date, 11: float, 12: str, 13: str}
    sql_str = f"INSERT INTO user_info VALUES({'%s,' * 13}%s)"
    datas = []
    for i in range(1, rows):
        row_data = []
        for j in range(0, cols):
            cell = sheet.cell(i, j)
            cell_value = cell.value
            if j == 10:
                row_data.append(cell_value)
                continue
            # print(j)
            method = attribute_method.get(j)
            value = method(cell_value)
            row_data.append(value)
        # print(row_data)
        value_tuple = tuple(row_data)
        datas.append(value_tuple)
    create_table()
    dbManager.insert_datas(sql_str, datas)


test2()
